clear all
set more off
capture log close
log using "$output/tableA4.log", replace

version 16	

use "$data/acsmergeddata.dta", clear

*** total adults
gen pop=(perwt + spouseperwt)
egen totadults=sum(pop)
di totadults
drop pop

gen pop=(newnumkidswt17)
egen totkids=sum(pop)
di totkids
drop pop

gen all=1
replace newnumkidswt17=round(newnumkidswt17,1)
bysort statefip: egen stnumkids=sum(newnumkidswt17)

gen poorkids=(poor==1)*newnumkidswt17
bysort statefip: egen stpoorkids=sum(poorkids)
sum stpoorkids

gen stillpoor=(poor==1 & poorctc21==1)*newnumkidswt17
bysort statefip: egen ststillpoor=sum(stillpoor)
sum ststillpoor

table statefip [fw=newnumkidswt17], c(n all mean faminc mean stpoorkids mean ststillpoor) 

gen adultsstopwork=.
gen dynamicantipovkid=.

levelsof statefip, local(levels)
	foreach l of local levels {
	di "-> statefip = `: label (statefip) `l''"

* row 1
gen group=(married==0)*(incwage>0)*(v25>0)*(agi2017<80000)*(calckids>0)*(samplejustwomen==1)*(statefip==`l')
gen group1=perwt*avgtax*.4*group
egen totgroup1a=sum(group1)

drop group*

* row 2
gen group=(married==0)*(incwage>0)*(v25==0)*(agi2017<80000)*(calckids>0)*(samplejustwomen==1)*(statefip==`l')

gen group1=perwt*avgtax*.2*group
egen totgroup1b=sum(group1)

drop group*

* row 3
gen group=(married==1)*(incwage>0)*(agi2017<80000)*(calckids>0)*(samplejustwomen==1)*(statefip==`l')

gen group1=perwt*avgtax*.2*group
egen totgroup1c=sum(group1)

drop group*

* row 4
gen group=(married==1)*(totincwage>incwage)*(agi2017<80000)*(calckids>0)*(samplejustwomen==1)*(statefip==`l')

gen group1=perwt*avgtax*.05*group
egen totgroup1d=sum(group1)

drop group*


* row 5: sum up non-mom and potential spouse
gen person1=(incwage>0)*(agi2017<80000)*(calckids>0)*(samplenotmoms==1)*(statefip==`l')
gen person2=(totincwage>incwage)*(agi2017<80000)*(calckids>0)*(samplenotmoms==1)*(married==1)*(statefip==`l')

gen group1=(perwt*person1+spouseperwt*person2)*avgtax*.05
egen totgroup1e=sum(group1)

drop group*

forvalues i=1/1{
	di totgroup`i'a+totgroup`i'b+totgroup`i'c+totgroup`i'd+totgroup`i'e
}

replace adultsstopwork=totgroup1a+totgroup1b+totgroup1c+totgroup1d+totgroup1e if statefip==`l'
drop totgroup* person*
	}


table statefip [fw=newnumkidswt17], c(n all mean faminc mean stpoorkids mean ststillpoor mean adultsstopwork)	


levelsof statefip, local(levels)
	foreach l of local levels {
	di "-> statefip = `: label (statefip) `l''"

gen group=(married==0)*(incwage>0)*(v25>0)*(agi2017<80000)*(calckids>0)*(samplejustwomen==1)*(statefip==`l')

gen group4=newnumkidswt17*avgtax*.4*group*(poor==0)*poorctc21nother
egen totgroup4a=sum(group4)

gen group5=newnumkidswt17*avgtax*.4*group*(poor==1)*(poorctc21==0)*poorctc21nother
egen totgroup5a=sum(group5)

drop group*

* row 2
gen group=(married==0)*(incwage>0)*(v25==0)*(agi2017<80000)*(calckids>0)*(samplejustwomen==1)*(statefip==`l')

gen group4=newnumkidswt17*avgtax*.2*group*(poor==0)*poorctc21nother
egen totgroup4b=sum(group4)

gen group5=newnumkidswt17*avgtax*.2*group*(poor==1)*(poorctc21==0)*poorctc21nother
egen totgroup5b=sum(group5)

drop group*

* row 3
gen group=(married==1)*(incwage>0)*(agi2017<80000)*(calckids>0)*(samplejustwomen==1)*(statefip==`l')

gen group4=newnumkidswt17*avgtax*.2*group*(poor==0)*poorctc21nother
egen totgroup4c=sum(group4)

gen group5=newnumkidswt17*avgtax*.2*group*(poor==1)*(poorctc21==0)*poorctc21nother
egen totgroup5c=sum(group5)

drop group*

* row 4
gen group=(married==1)*(totincwage>incwage)*(agi2017<80000)*(calckids>0)*(samplejustwomen==1)*(statefip==`l')

gen group4=newnumkidswt17*avgtax*.05*group*(poor==0)*poorctc21notspouse
egen totgroup4d=sum(group4)

gen group5=newnumkidswt17*avgtax*.05*group*(poor==1)*(poorctc21==0)*poorctc21notspouse
egen totgroup5d=sum(group5)

drop group*


* row 5: sum up non-mom and potential spouse
gen person1=(incwage>0)*(agi2017<80000)*(calckids>0)*(samplenotmoms==1)*(statefip==`l')
gen person2=(totincwage>incwage)*(agi2017<80000)*(calckids>0)*(samplenotmoms==1)*(married==1)*(statefip==`l')

gen group4=(newnumkidswt17*person1)*avgtax*.05*(poor==0)*poorctc21nother+(newnumkidswt17*person2)*avgtax*.05*(poor==0)*poorctc21notspouse
egen totgroup4e=sum(group4)

gen group5=(newnumkidswt17*person1)*avgtax*.05*(poor==1)*(poorctc21==0)*poorctc21nother+newnumkidswt17*person2*avgtax*.05*(poor==1)*(poorctc21==0)*poorctc21notspouse
egen totgroup5e=sum(group5)

drop group*

di (ststillpoor+totgroup4a+totgroup4b+totgroup4c+totgroup4d+totgroup4e+totgroup5a+totgroup5b+totgroup5c+totgroup5d+totgroup5e) 

replace dynamicantipovkid=ststillpoor+totgroup4a+totgroup4b+totgroup4c+totgroup4d+totgroup4e+totgroup5a+totgroup5b+totgroup5c+totgroup5d+totgroup5e if statefip==`l'

drop totgroup* person*

}	

gen poorrate1=100*stpoorkids/stnumkids
gen poorrate2=100*ststillpoor/stnumkids
gen poorrate3=100*dynamicantipovkid/stnumkids

table statefip [fw=newnumkidswt17], c(n all mean faminc mean stpoorkids mean ststillpoor mean dynamicantipovkid ) 

table statefip [fw=newnumkidswt17], c(mean adultsstopwork mean poorrate1 mean poorrate2 mean poorrate3 )

log close
